﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Oracle.DataAccess.Client;

namespace Project01_DBSecurity
{
	public partial class Form_Role_SysPrivs : Form
	{
		private int type;
		private string role_name;
		private int nRevoke;
		private string[] revoke = new string[100];

		public Form_Role_SysPrivs(int type, string role_name)
		{
			InitializeComponent();
			this.type = type;
			this.role_name = role_name;
			this.nRevoke = 0;
			this.Text = "Role : " + role_name;
			switch (type)
			{
				case 1:
					{
						OracleCommand comm = new OracleCommand();
						comm.Connection = orcl.conn;
						comm.CommandText = "select * from USER_SYS_PRIVS where ADMIN_OPTION = 'YES'";
						try
						{
							OracleDataReader reader = comm.ExecuteReader();
							while (reader.Read())
							{
								list_available.Items.Add(reader["PRIVILEGE"].ToString());
							}
							reader.Close();
							reader.Dispose();
							comm.Dispose();
						}
						catch (Exception ex)
						{
							MessageBox.Show(ex.Message, "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
						}
						comm.CommandText = "select * from ROLE_SYS_PRIVS where ROLE = '" + role_name + "'";
						try
						{
							OracleDataReader reader = comm.ExecuteReader();
							while (reader.Read())
							{
								ListViewItem lvit = list_current.Items.Add(reader["PRIVILEGE"].ToString());
								if (reader["ADMIN_OPTION"].ToString() == "YES")
									lvit.Checked = true;
							}
							reader.Close();
							reader.Dispose();
							comm.Dispose();
						}
						catch (Exception ex)
						{
							MessageBox.Show(ex.Message, "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
						}
					}
					break;
			}
		}

		private void btOK_Click(object sender, EventArgs e)
		{
			OracleCommand comm = new OracleCommand();
			comm.Connection = orcl.conn;

			for (int i = 0; i < nRevoke; i++)
			{
				comm.CommandText = revoke[i];
				try
				{
					comm.ExecuteNonQuery();
				}
				catch (Exception ex)
				{
				}
			}

			for (int i = 0; i < list_current.Items.Count; i++)
			{
				comm.CommandText = "revoke " + list_current.Items[i].Text + " from " + role_name;
				try
				{
					comm.ExecuteNonQuery();
				}
				catch (Exception ex)
				{
				}
				comm.CommandText = "grant " + list_current.Items[i].Text + " to " + role_name;
				if (list_current.Items[i].Checked == true)
					comm.CommandText += " with admin option";
				try
				{
					comm.ExecuteNonQuery();
				}
				catch (Exception ex)
				{
				}
			}

			this.Close();
		}

		private void btCancel_Click(object sender, EventArgs e)
		{
			this.Close();
		}

		private void select_Click(object sender, EventArgs e)
		{
			if (list_available.SelectedItems.Count > 0)
			{
				bool had = false;
				string syspriv = list_available.SelectedItems[0].Text;
				for (int i = 0; i < list_current.Items.Count; i++)
					if (list_current.Items[i].Text == syspriv)
					{
						had = true;
						break;
					}
				if (had == false)
					list_current.Items.Add(syspriv);
			}
		}

		private void deselect_Click(object sender, EventArgs e)
		{
			if (list_current.SelectedItems.Count > 0)
			{
				revoke[nRevoke] = "revoke " + list_current.SelectedItems[0].Text + " from " + role_name;
				nRevoke++;
				list_current.Items.Remove(list_current.SelectedItems[0]);
			}
		}
	}
}
